package com.game.framework.orm.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.game.utils.log.Log;


/**
 * JdbcSupport
 * @author Liyuan
 */

public abstract class JdbcSupport{
	/** logger */
	private static Logger logger = Logger.getLogger(JdbcSupport.class);
	/** MAX_BATCH */
	protected static final Integer MAX_BATCH = 500;
	/** dataSource */
	protected DataSource dataSource;
	

	//execute
	/**
	 * executeUpdate
	 * @param sql
	 * @param objs
	 */
	public Boolean executeUpdate(String sql, Object... objs){
		logger.debug(sql);
		Connection conn = null;
		PreparedStatement stmt = null;
		Boolean flag = false;
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			if(objs != null && objs.length > 0){
				int index = 1;
				for(Object o : objs){
					stmt.setObject(index++, o);
				}
			}
			stmt.executeUpdate();
			logger.debug("ExecuteUpdate Success.");
			flag = true;
		} catch (Exception e) {
			logger.warn("ExecuteUpdate Failed.");
			Log.printStackTrace(e);
		} finally {
			this.closeStatement(stmt);
			this.closeConnection(conn);
		}
		return flag;
	}
	
	/**
	 * execute
	 * @param stmt
	 * @param sql
	 * @param objs
	 * @throws SQLException
	 */
	public void execute(PreparedStatement stmt, String sql, Object... objs) throws SQLException{
		logger.debug(sql);
		if(stmt == null) return;
		try {
			if(objs != null && objs.length > 0){
				int index = 1;
				for(Object o : objs){
					stmt.setObject(index++, o);
				}
			}
			stmt.executeUpdate();
			this.closeStatement(stmt);
		} catch (SQLException e) {
			this.closeStatement(stmt);
			throw e;
		}
	}
	
	/**
	 * executeAndReturnID
	 * @param stmt
	 * @param sql
	 * @param objs
	 * @throws SQLException
	 */
	public Long executeAndReturnID(Statement stmt, String sql) throws SQLException{
		logger.debug(sql);
		Long id = 0l;
		ResultSet rs = null;
		try {
			stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = stmt.getGeneratedKeys();
			if(rs.next()){
				id = (Long) rs.getObject(1);
			}
			this.closeStatement(stmt);
		} catch (SQLException e) {
			closeResultSet(rs);
			this.closeStatement(stmt);
			throw e;
		}
		return id;
	}
	
	/**
	 * executeNotCloseStatement
	 * @param stmt
	 * @param sql
	 * @param objs
	 * @throws SQLException
	 */
	public void executeNotCloseStatement(PreparedStatement stmt, String sql, Object... objs) throws SQLException{
		logger.debug(sql);
		if(stmt == null) return;
		try {
			if(objs != null && objs.length > 0){
				int index = 1;
				for(Object o : objs){
					stmt.setObject(index++, o);
				}
			}
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * executeAndReturnIDNotCloseStatement
	 * @param stmt
	 * @param sql
	 * @param objs
	 * @throws SQLException
	 */
	public Long executeAndReturnIDNotCloseStatement(Statement stmt, String sql) throws SQLException{
		logger.debug(sql);
		Long id = 0l;
		ResultSet rs = null;
		try {
			stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = stmt.getGeneratedKeys();
			if(rs.next()){
				id = (Long) rs.getObject(1);
			}
		} catch (SQLException e) {
			closeResultSet(rs);
			throw e;
		}
		return id;
	}
	
	/**
	 * executeBatch
	 * @param sqls
	 * @return
	 */
	public Boolean executeBatch(String... sqls){
		if(sqls == null || sqls.length < 1) return false;
		Connection conn = null;
		Statement stmt = null;
		Boolean flag = false;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.length; ++i) {
				stmt.addBatch(sqls[i]);
				if(i % MAX_BATCH == 0){
					stmt.executeBatch();
				}
			}
			stmt.executeBatch();
			conn.commit();
			logger.debug("ExecuteBatch Success.");
			flag = true;
		} catch (Exception e) {
			logger.warn("ExecuteBatch Failed.");
			rollback(conn);
			Log.printStackTrace(e);
		} finally {
			closeStatement(stmt);
			closeConnection(conn);
		}
		return flag;
	}
	
	/**
	 * batch
	 * @param sqls
	 * @return
	 */
	public Boolean executeBatch(List<String> sqls){
		if(sqls == null || sqls.size() < 1) return false;
		Connection conn = null;
		Statement stmt = null;
		Boolean flag = false;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.size(); ++i) {
				stmt.addBatch(sqls.get(i));
				if(i % MAX_BATCH == 0){
					stmt.executeBatch();
				}
			}
			stmt.executeBatch();
			conn.commit();
			logger.debug("ExecuteBatch Success.");
			flag = true;
		} catch (Exception e) {
			logger.warn("ExecuteBatch Failed.");
			rollback(conn);
			Log.printStackTrace(e);
		} finally {
			closeStatement(stmt);
			closeConnection(conn);
		}
		return flag;
	}
	
	/**
	 * executeQuery
	 * @param sql
	 * @param objs
	 * @return
	 */
	public ResultSet executeQuery(String sql, Object... objs){
		logger.debug(sql);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			if(objs != null && objs.length > 0){
				int index = 1;
				for(Object o : objs){
					stmt.setObject(index++, o);
				}
			}
			rs = stmt.executeQuery();
			logger.debug("ExecuteQuery Success.");
		} catch (Exception e) {
			logger.warn("ExecuteQuery Failed.");
			Log.printStackTrace(e);
		} finally {
			if(rs == null){
				closeStatement(stmt);
				closeConnection(conn);
			}
		}
		return rs;
	}
	
//	/**
//	 * executeQuery
//	 * 说明：CachedRowSetImpl测试未通过，因此该方法暂时保留
//	 * @param sql
//	 * @param objs
//	 * @return
//	 */
//	public CachedRowSet executeQuery(String sql, Object... objs){
//		return null;
//	}
	
	/**
	 * saveAndReturnID
	 * @param sql
	 * @return
	 */
	public Long executeAndReturnID(String sql){
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Long id = 0l;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = stmt.getGeneratedKeys();
			if(rs.next()){
				Object obj = rs.getObject(1);
				if(obj != null) id = (Long) obj;
			}
			logger.debug("Save Success.");
		} catch (Exception e) {
			logger.warn("Save Failed.");
			Log.printStackTrace(e);
		} finally {
			closeResultSet(rs);
			closeStatement(stmt);
			closeConnection(conn);
		}
		return id;
	}
	
	/**
	 * executeQueryAndReturnSingleResult
	 * @param sql
	 * @return
	 */
	public Object executeQueryAndReturnSingleResult(String sql, Object... objs){
		logger.debug(sql);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Object obj = null;
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			if(objs != null && objs.length > 0){
				int index = 1;
				for(Object o : objs){
					stmt.setObject(index++, o);
				}
			}
			rs = stmt.executeQuery();
			if(rs.next()){
				obj = rs.getObject(1);
				logger.debug("GetValue Success.");
			}
		} catch (Exception e) {
			logger.warn("GetValue Failed.");
			Log.printStackTrace(e);
		} finally {
			closeResultSet(rs);
			closeStatement(stmt);
			closeConnection(conn);
		}
		return obj;
	}
	
	
	//封装函数
	/**
	 * add
	 * @param sql
	 * @return
	 */
	public Boolean add(String sql, Object... objs){
		return this.executeUpdate(sql, objs);
	}
	
	/**
	 * update
	 * @param sql
	 * @param objs
	 * @return
	 */
	public Boolean update(String sql, Object... objs){
		return this.executeUpdate(sql, objs);
	}
	
	/**
	 * delete
	 * @param sql
	 * @param objs
	 * @return
	 */
	public Boolean delete(String sql, Object... objs){
		return this.executeUpdate(sql, objs);
	}
	
	/**
	 * truncate
	 * @param tableName
	 * @return
	 */
	public Boolean truncate(String tableName){
		String sql = "TRUNCATE " + tableName;
		return this.executeUpdate(sql);
	}
	
	/**
	 * getValue
	 * @param sql
	 * @param objs
	 * @return
	 */
	public Object getValue(String sql, Object... objs){
		return this.executeQueryAndReturnSingleResult(sql, objs);
	}
	
	/**
	 * count
	 * @param sql
	 * @param objs
	 * @return 异常返回-1
	 */
	public Number count(String sql, Object... objs){
		Object obj = this.executeQueryAndReturnSingleResult(sql, objs);
		return obj == null ? -1 : (Number) obj;
	}
	
	
	//utils
	/**
	 * getConnection
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException{
		return dataSource.getConnection();
	}
	
	/**
	 * closeConnection
	 * @param conn
	 */
	public void closeConnection(Connection conn) {
		try {
			if (conn == null || conn.isClosed()) return;
			//conn.close(); //关闭连接
			//logger.debug("Close Connection Success.");
			((DataSourceImpl)dataSource).releaseConnection(conn); //释放连接
			logger.debug("Release Connection Success.");
		} catch (SQLException e) {
			logger.warn("Close Connection Failed.");
			Log.printStackTrace(e);
		}
	}
	
	/**
	 * closeStatement
	 * @param stmt
	 */
	public void closeStatement(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
				logger.debug("Close Statement Success.");
			}
		} catch (SQLException e) {
			logger.warn("Close Statement Failed.");
			Log.printStackTrace(e);
		}
	}
	
	/**
	 * closeResultSet
	 * @param rs
	 */
	public void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				logger.debug("Close ResultSet Success.");
				rs.close();
			}
		} catch (SQLException e) {
			logger.warn("Close ResultSet Failed.");
			Log.printStackTrace(e);
		}
	}
	
	/**
	 * closeAll
	 * @param rs
	 */
	public void closeAll(ResultSet rs){
		Connection conn = null;
		Statement stmt = null;
		try {
			if (rs != null) {
				stmt = rs.getStatement();
				closeResultSet(rs);
				if(stmt != null){
					conn = stmt.getConnection();
					this.closeStatement(stmt);
					this.closeConnection(conn);
				}
			}
		} catch (SQLException e) {
			Log.printStackTrace(e);
		}
	}
	
	/**
	 * rollback
	 * @param conn
	 */
	public void rollback(Connection conn){
		try {
			conn.rollback();
		} catch (Exception e) {
			logger.warn("Rollback Success.");
			Log.printStackTrace(e);
		}
	}
	
	//setter
	/** setDataSource */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
}
